import sqlite3


def execute_sql(sql):
    conn = get_conn()
    cursor = conn.cursor()
    rs = cursor.execute(sql)
    for item in rs:
        print(item)


def get_conn():
    return sqlite3.connect("schools.db")


def sel_1():
    """
    1、查询出所有学习人工智能基础课程的同学分数、姓名、班级名字、年级名字，然后按照分数从大到小排列
    :return:
    """
    sql = "select s.student_name,sc.score,c.course_name,class.class_name,g.grade_name from " \
          "student_course sc,course c ,student s,class,grade g  " \
          "where sc.course_no = c.course_no and sc.student_no = s.student_no " \
          "and s.class_no = class.class_no and  class.grade_no = g.grade_no " \
          "and c.course_name = '人工智能基础' order by  sc.score asc "
    execute_sql(sql)


def sel_2():
    """
    2、查询出每个年级的每个班分别有多少学生
    :return:
    """
    sql = "select count(1) '学生人数',c.class_name from student s, class c " \
          "where s.class_no = c.class_no group by  s.class_no"
    execute_sql(sql)


def sel_3():
    """
    3、查询出每一个科目最高(max)得分的学生是谁
    :return:
    """
    sql = "select max(sc.score),s.student_name,c.course_name " \
          "from student_course sc,student s ,course c " \
          "where sc.student_no = s.student_no and sc.course_no = c.course_no group by sc.course_no"
    execute_sql(sql)


def sel_4():
    """
    4、查询出每一个科目最低(min)得分的学生是谁
    :return:
    """
    sql = "select min(sc.score),s.student_name,c.course_name " \
          "from student_course sc,student s ,course c " \
          "where sc.student_no = s.student_no and sc.course_no = c.course_no group by sc.course_no"
    execute_sql(sql)


def sel_5():
    """
    5、计算该校学生哪一个科目的成绩最稳定，就是计算成绩的标准差最小。标准差可以数据分析中非常常用且重要的指标哦。
    :return:
    """
    sql = "select sum(power(avg_score-sc2.score,2))/count(1) fangcha,sc2.student_no,r.student_name from student_course sc2," \
          "(select avg(sc.score) avg_score,s.student_name,s.student_no from student_course sc,student s where sc.student_no = s.student_no  group by sc.student_no) as r " \
          "where sc2.student_no = r.student_no group by  sc2.student_no order by fangcha asc limit 0,1"
    execute_sql(sql)


if __name__ == "__main__":
    # sel_1()
    # sel_2()
    # sel_3()
    # sel_4()
    sel_5()